﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;

/// <summary>
/// Summary description for ingredientDAO
/// </summary>
public class ingredientDAO : dataAccess
{
    public ingredientDAO()
    {
    }
    public DataSet get()
    {
        String sql = "SELECT * FROM Ingredient";
        return getData(sql);
    }
    public DataSet getByUser(String UserID)
    {
        String sql = "SELECT * FROM Ingredient i, IngredientCategory c, Animal a  WHERE UserID='" + UserID
            + "' AND i.AnimalID = a.AnimalID AND i.CategoryID = c.CategoryID ";
        return getData(sql);
    }
    public void addNew(Ingredient ingredient, String UserID)
    {
        String sql = String.Format("insert into Ingredient values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}')",
            ingredient.IngredientID1, UserID, ingredient.Name1, ingredient.CategoryID1,
            ingredient.Price1, ingredient.Dry1, ingredient.AnimalID1, ingredient.Code1, ingredient.InStock1);
        executeQuery(sql);
    }
    public void delete(Ingredient ingredient, String UserID)
    {
        String sql = "delete from Ingredient where IngredientID = '" + ingredient.IngredientID1 + "'";
        executeQuery(sql);
    }
    public void update(Ingredient ingredient)
    {
        try
        {

        
        String sql = "update Ingredient set IngredientName = '" + ingredient.Name1 + 
            "', Price = '" + ingredient.Price1 + "',Dry = '" + ingredient.Dry1 + "',AnimalID = '" + ingredient.AnimalID1 + 
            "',CategoryID = '" + ingredient.CategoryID1 + "',Code = '" + ingredient.Code1 + 
            "',InStock = '" + ingredient.InStock1 + "' where IngredientID = '" + ingredient.IngredientID1 + "'";
        executeQuery(sql);
        }
        catch (Exception)
        {

            throw;
        }
    }
    public DataSet getcbbAnimal()
    {
        string sql = "SELECT AnimalName AS 'Animal',AnimalID FROM Animal";
        return getData(sql);
    }
    public DataSet getcbbCategory()
    {
        string sql = "SELECT CategoryName AS 'Category',CategoryID FROM IngredientCategory ";
        return getData(sql);
    }
    public DataSet getclbNutrient()
    {
        string sql = "SELECT NutrientName AS 'Nutrient',NutrientID FROM Nutrient ";
        return getData(sql);
    }
    public DataSet getIngredient(Ingredient ingredient)
    {
        string sql = "select * from Ingredient i, Animal a, IngredientCategory c where IngredientID = '" + ingredient.IngredientID1 + "' AND i.AnimalID = a.AnimalID AND i.CategoryID = c.CategoryID ";
        return getData(sql);
    }
    public void addComposition(String IngredientID, String NutrientID, float CompositionValue) {
        try
        {
            String sql = String.Format("insert into Composition values ('{0}','{1}','{2}')",NutrientID, IngredientID, CompositionValue);
            executeQuery(sql);
        }
        catch (Exception)
        {
            
            throw;
        }
        
    }
    public DataSet getComposition(String ingredientID)
    {
        string sql="SELECT NutrientName, CompositionValue FROM Ingredient i, Composition c, Nutrient n WHERE i.IngredientID='"+ingredientID+"' AND c.IngredientID=i.IngredientID AND c.NutrientID=n.NutrientID";
        return getData(sql);
    }
}